package org.stvd.core.util;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.TimeZone;

import jxl.Cell;
import jxl.CellType;
import jxl.DateCell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

public class ExcelUtil {

    public static List<String> insert(InputStream bookStream) {
        List<String> listSQL = new ArrayList<String>();
        try {
            // 选取指定的excel
            Workbook workbook = Workbook.getWorkbook(bookStream);
            // Workbook workbook = Workbook.getWorkbook(new File(filename));
            // 选取制定的sheet
            Sheet sheet = workbook.getSheet(0);
            // 获得sheet名：表名
            System.out.println("sheet name is " + sheet.getName());
            String tableName = sheet.getName();
            // 获得字段名,第一行为 ： 字段名
            StringBuilder fields = new StringBuilder();
            for (int i = 0; i < sheet.getColumns(); i++) {
                fields.append(sheet.getCell(i, 0).getContents().trim());
                if (i < sheet.getColumns() - 1) {
                    fields.append(',');
                }
            }

            // 遍历循环得到所要的cell值
            for (int j = 1; j < sheet.getRows(); j++) {
                // 对应的字段值
                StringBuilder values = new StringBuilder();
                for (int i = 0; i < sheet.getColumns(); i++) {
                    // 获取该cell的值
                    Cell cell = sheet.getCell(i, j);
                    String cellValue = cell.getContents().trim();

                    CellType cellType = cell.getType();
                    if (cellType == CellType.DATE) {
                        DateCell dateCell = (DateCell) cell;
                        Date dateValue = dateCell.getDate();
                        TimeZone tz = TimeZone.getTimeZone("GMT");
                        DateUtil.Y2DTIME_FORMAT.setTimeZone(tz);
                        cellValue = DateUtil.Y2DTIME_FORMAT.format(dateValue);

                    }

                    values.append('\'').append(cellValue).append('\'');
                    if (i < sheet.getColumns() - 1) {
                        values.append(',');
                    }
                }

                String sql = "insert into " + tableName + "(" + fields.toString() + ") values(" + values + ")";
                System.out.println("insert sql is : " + sql);
                listSQL.add(sql);
            }
            workbook.close();
        } catch (BiffException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return listSQL;
    }

    public static List<LinkedHashMap<String, String>> excelToList(InputStream bookStream) {
        List<LinkedHashMap<String, String>> list = new ArrayList<LinkedHashMap<String, String>>();
        try {
            // 选取指定的excel
            Workbook workbook = Workbook.getWorkbook(bookStream);
            // 选取制定的sheet
            Sheet sheet = workbook.getSheet(0);

            // 遍历循环得到所要的cell值
            for (int j = 1; j < sheet.getRows(); j++) {
                // 对应的字段值
                LinkedHashMap<String, String> rowData = new LinkedHashMap<String, String>();
                for (int i = 0; i < sheet.getColumns(); i++) {
                    String field = sheet.getCell(i, 0).getContents().trim();
                    Cell cell = sheet.getCell(i, j);
                    String cellValue = cell.getContents().trim();

                    CellType cellType = cell.getType();
                    if (cellType == CellType.DATE) {
                        DateCell dateCell = (DateCell) cell;
                        Date dateValue = dateCell.getDate();
                        TimeZone tz = TimeZone.getTimeZone("GMT");
                        DateUtil.Y2DTIME_FORMAT.setTimeZone(tz);
                        cellValue = DateUtil.Y2DTIME_FORMAT.format(dateValue);
                    }

                    rowData.put(field, cellValue);
                }
                list.add(rowData);
            }
            workbook.close();
        } catch (BiffException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return list;
    }

//    public static void main(String[] args) {
        // insert("D:\\test.xls");
//    }
}